create database geoip;

use geoip;

create table country (
	cc CHAR(2) NOT NULL,
	country VARCHAR(100) NOT NULL,
	primary key (cc)
	);

create table location (
	locId INT UNSIGNED NOT NULL,
	country CHAR(2) NOT NULL,
	region CHAR(2),
	city VARCHAR(50),
	postalCode VARCHAR(8),
	latitude DECIMAL(6,4),
	longitude DECIMAL(7,4),
	dmaCode INT(4) UNSIGNED,
	areaCode INT(4) UNSIGNED,
	primary key (locId),
	foreign key (country) references country (cc) on delete cascade
	);

CREATE TABLE blocks (
  start_ip INT UNSIGNED NOT NULL,
  end_ip INT UNSIGNED NOT NULL,
  locId INT UNSIGNED NOT NULL,
	primary key (start_ip),
	foreign key (locId) references location (locId) on delete cascade
);

flush privileges;
grant select on geoip.* to geoippub identified by 'public';
flush privileges;

commit;